const sqlite3 = require('sqlite3').verbose();
const path = require('path');
const fs = require('fs');

// 数据库文件路径
const DB_PATH = process.env.DB_PATH || path.join(__dirname, '../../../data/novels.db');

// 数据库连接
let db;

// 数据库查询执行函数
function runQuery(sql, params = []) {
  console.log(`[database] 执行更新: ${sql}, 参数:`, params);
  const startTime = Date.now();
  
  return new Promise((resolve, reject) => {
    db.run(sql, params, function(err) {
      const duration = Date.now() - startTime;
      
      if (err) {
        console.error(`[database] 更新失败 (${duration}ms): ${sql}`, err);
        reject(err);
        return;
      }
      
      console.log(`[database] 更新成功 (${duration}ms): ${sql}, lastID: ${this.lastID}, changes: ${this.changes}`);
      resolve({
        lastID: this.lastID,
        changes: this.changes
      });
    });
  });
}

// 数据库查询获取所有结果函数
function getAllQuery(sql, params = []) {
  console.log(`[database] 执行查询: ${sql}, 参数:`, params);
  const startTime = Date.now();
  
  return new Promise((resolve, reject) => {
    db.all(sql, params, (err, rows) => {
      const duration = Date.now() - startTime;
      
      if (err) {
        console.error(`[database] 查询失败 (${duration}ms): ${sql}`, err);
        reject(err);
        return;
      }
      
      console.log(`[database] 查询成功 (${duration}ms): ${sql}, 返回 ${rows ? rows.length : 0} 行`);
      resolve(rows || []);
    });
  });
}

// 数据库查询获取单个结果函数
function getQuery(sql, params = []) {
  console.log(`[database] 执行单行查询: ${sql}, 参数:`, params);
  const startTime = Date.now();
  
  return new Promise((resolve, reject) => {
    db.get(sql, params, (err, row) => {
      const duration = Date.now() - startTime;
      
      if (err) {
        console.error(`[database] 单行查询失败 (${duration}ms): ${sql}`, err);
        reject(err);
        return;
      }
      
      console.log(`[database] 单行查询成功 (${duration}ms): ${sql}, 返回: ${row ? '找到数据' : '无数据'}`);
      resolve(row);
    });
  });
}

/**
 * 初始化数据库
 */
async function setupDatabase() {
  console.log('[database] 开始初始化数据库...');
  
  return new Promise((resolve, reject) => {
    // 确保数据库目录存在
    const dbDir = path.dirname(DB_PATH);
    if (!fs.existsSync(dbDir)) {
      console.log(`[database] 创建数据库目录: ${dbDir}`);
      fs.mkdirSync(dbDir, { recursive: true });
    }
    
    // 配置数据库
    db = new sqlite3.Database(DB_PATH, sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, async (err) => {
      if (err) {
        console.error('[database] 数据库连接错误:', err.message);
        reject(err);
        return;
      }
      
      console.log('[database] 已连接到SQLite数据库');
      
      try {
        // 开启WAL模式提高性能
        await runQuery('PRAGMA journal_mode = WAL;');
        
        // 开启外键约束
        await runQuery('PRAGMA foreign_keys = ON;');
        
        // 优化查询
        await runQuery('PRAGMA synchronous = NORMAL;');
        await runQuery('PRAGMA temp_store = MEMORY;');
        await runQuery('PRAGMA cache_size = 10000;');
        
        // 创建小说表
        await runQuery(`
          CREATE TABLE IF NOT EXISTS novels (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            path TEXT NOT NULL UNIQUE,
            name TEXT NOT NULL,
            size INTEGER NOT NULL,
            modified DATETIME NOT NULL,
            added DATETIME,
            created DATETIME DEFAULT CURRENT_TIMESTAMP
          )
        `);
        
        // 添加索引
        await runQuery('CREATE INDEX IF NOT EXISTS idx_novels_path ON novels(path);');
        await runQuery('CREATE INDEX IF NOT EXISTS idx_novels_name ON novels(name);');
        
        // 创建扫描缓存表
        await runQuery(`
          CREATE TABLE IF NOT EXISTS scan_cache (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            last_scan_time TEXT NOT NULL,
            novels_count INTEGER NOT NULL,
            cache_valid BOOLEAN NOT NULL DEFAULT 1,
            data_hash TEXT
          )
        `);
        
        resolve();
      } catch (err) {
        console.error('[database] 数据库初始化失败:', err.message);
        reject(err);
      }
    });
  });
}

/**
 * 获取数据库连接
 */
function getDb() {
  if (!db) {
    console.warn('[database] 警告: 尝试在初始化前获取数据库实例');
    throw new Error('数据库未初始化');
  }
  return db;
}

/**
 * 关闭数据库连接
 */
function closeDatabase() {
  return new Promise((resolve, reject) => {
    if (db) {
      console.log('[database] 关闭数据库连接...');
      db.close((err) => {
        if (err) {
          console.error('[database] 关闭数据库连接失败:', err.message);
          reject(err);
          return;
        }
        
        console.log('[database] 数据库连接已关闭');
        db = null;
        resolve();
      });
    } else {
      console.log('[database] 数据库连接已关闭或未初始化');
      resolve();
    }
  });
}

/**
 * 获取最新的扫描缓存信息
 * @returns {Promise<Object>} 扫描缓存信息
 */
async function getLastScanCache() {
  console.log('[database] 获取最新扫描缓存');
  
  try {
    const sql = `
      SELECT * FROM scan_cache 
      WHERE cache_valid = 1 
      ORDER BY last_scan_time DESC 
      LIMIT 1
    `;
    
    const cache = await getQuery(sql);
    return cache;
  } catch (err) {
    console.error('[database] 获取扫描缓存失败:', err);
    return null;
  }
}

/**
 * 更新扫描缓存
 * @param {number} count 小说数量
 * @param {string} hash 数据哈希值
 * @returns {Promise<Object>} 数据库操作结果
 */
async function updateScanCache(count, hash = null) {
  console.log(`[database] 更新扫描缓存: count=${count}`);
  
  try {
    // 将之前的缓存标记为无效
    await runQuery(`UPDATE scan_cache SET cache_valid = 0`);
    
    // 添加新的缓存记录
    const sql = `
      INSERT INTO scan_cache (last_scan_time, novels_count, data_hash)
      VALUES (?, ?, ?)
    `;
    
    const currentTime = new Date().toISOString();
    const result = await runQuery(sql, [currentTime, count, hash]);
    
    console.log(`[database] 扫描缓存更新成功: 小说数=${count}, 时间=${currentTime}`);
    return result;
  } catch (err) {
    console.error('[database] 更新扫描缓存失败:', err);
    throw err;
  }
}

/**
 * 将缓存标记为无效
 * @returns {Promise<Object>} 数据库操作结果
 */
async function invalidateScanCache() {
  console.log('[database] 标记所有扫描缓存为无效');
  
  try {
    const result = await runQuery(`UPDATE scan_cache SET cache_valid = 0`);
    return result;
  } catch (err) {
    console.error('[database] 标记缓存无效失败:', err);
    throw err;
  }
}

// 导出
module.exports = {
  setupDatabase,
  getDb,
  closeDatabase,
  runQuery,
  getAllQuery,
  getQuery,
  getLastScanCache,
  updateScanCache,
  invalidateScanCache
}; 